package com.example.springboot.utils.jdbc;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;

public class StudentDaoTest {

	@Test
	public void getALL() throws Exception {
		C3P0Util baseDao = new C3P0Util();
		String sql = "select * from student where name=?";
		Student student = baseDao.getBean(sql, Student.class, "test01");
		System.out.println(student);

		// sql="delete from student where name=?";
		// baseDao.delete(sql, "test01");

		// sql="update student set name=? where id=?";
		// baseDao.update(sql,"test0000","0000");

		// sql = "insert into student(id,name,sex) values(?,?,?)";
		// baseDao.insert(sql, "001", "insert001", 1);

		sql = "select * from student ";
		List<Student> list = baseDao.getBeanList(sql, Student.class);
		System.out.println(list);

		List<Map<String, Object>> list2 = baseDao.getMapList(sql);
		System.out.println(list2);

		sql = "select count(id) from student ";
		Object count = baseDao.getCount(sql);
		System.out.println(count);
	}

	@Test
	public void findByNameAndPwd() {
		try {
			// ?在DButils中表示一个占位符
			long startTime = System.currentTimeMillis();
			// String sql = "select * from student where name=?";
			String sql = "select * from student ";
			Student student = C3P0Util.getQueryRuner().query(sql, new BeanHandler<Student>(Student.class), "test01");
			Map<String, Object> map = C3P0Util.getQueryRuner().query(sql, new MapHandler(), "test01");
			List<Student> query = C3P0Util.getQueryRuner().query(sql, new BeanListHandler<Student>(Student.class));
			List<Map<String, Object>> query2 = C3P0Util.getQueryRuner().query(sql, new MapListHandler());
			List<String> query3 = C3P0Util.getQueryRuner().query(sql, new ColumnListHandler<String>("name"));

			// 在这儿指定主键id为结果key，也可以传入列名 new KeyedHandler<Integer>("id")
			Map<String, Map<String, Object>> query4 = C3P0Util.getQueryRuner().query(sql, new KeyedHandler<String>(1));

			// new BeanMapHandler<Integer, Users>(Users.class,"id")
			Map<String, Student> query5 = C3P0Util.getQueryRuner().query(sql, new BeanMapHandler<String, Student>(Student.class, 1));
			// 这种情况下，以每行第一列为key
			// 用构建好的HashMap建立一个BeanProcessor对象
			BeanProcessor bean = new BeanProcessor();
			RowProcessor processor = new BasicRowProcessor(bean);
			Map<Integer, Student> rs = C3P0Util.getQueryRuner().query(sql, new BeanMapHandler<Integer, Student>(Student.class, processor));

			System.out.println("》》》》》》》查询耗时： " + (System.currentTimeMillis() - startTime));
			// System.out.println("BeanHandler: " + student);
			// System.out.println("MapHandler: " + map);
			System.out.println("BeanListHandler: " + query);
			System.out.println("MapListHandler: " + query2);
			System.out.println("ColumnListHandler: " + query3);
			System.out.println("KeyedHandler: " + query4);
			System.out.println("BeanMapHandler: " + query5);
			System.out.println("BeanMapHandler: " + rs);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Test
	public void addAddmin() {
		// 将数据插入到数据库中
		try {
			String sql = "insert into student(id,name,sex) values(?,?,?)";
			// ——batch是批处理。所以需要的参数是一个数组，这个数组第一个参数代表执行的次数，比如下面的5次，第二个参数代表需要的参数个数，下面是3个
			Object[][] params = new Object[5][];
			for (int i = 0; i < params.length; i++) {
				params[i] = new Object[] { "0" + i, "test" + i, 1 };
			}
			int[] batch = C3P0Util.getQueryRuner().batch(sql, params);
			// Map<String, Object> insert = C3P0Util.getQueryRuner().insert(sql,
			// new MapHandler(), "4", "test02", 1);
			// int res =
			// C3P0Util.getQueryRuner().update("insert into Student(id,name) values(?,?)",
			// 2, "test02");
			// System.out.println(res);
			System.out.println(Arrays.toString(batch));

		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("插入到数据库中操作失败"); // 应该将此信息发送到一个新的页面
		}
	}
}
